# %%
# 加载pandas
import pandas as pd

import reader
from common import *

# %%
# 读取数据
year, month = year_month()
df_gx = reader.read_csv(
    f"data/0-管线/{year}{month}_管线数据.zip",
    columns=("宽带号码", "接入模式", "合作模式", "七级地址ID"),
)
df_hg = reader.read_csv(
    f"data/0-互感/{year}{month}_互感数据.zip",
    columns=("宽带号码", "ONU下联端口", "所属ONU设备名称", "错误类型"),
)
df_bi = reader.read_csv(
    f"data/0-BI/{year}{month}_BI宽带月报.zip",
    columns=("宽带号码", "T减2月流量", "T减1月流量", "T月流量"),
)
df_bi["T减2月流量"] = df_bi["T减2月流量"].astype("float")
df_bi["T减1月流量"] = df_bi["T减1月流量"].astype("float")
df_bi["T月流量"] = df_bi["T月流量"].astype("float")

df = reader.read_csv(
    f"data/2-系统稽核/{year}{month}_宽带存量稽核明细.zip",
    columns=(
        "账期",
        "宽带号码",
        "合作模式",
        "接入模式",
        "业务类型",
        "5级地址ID",
        "网格名称",
        "系统稽核结果",
        "资源不准确存量用户",
        "一址多户用户（5户及以上）",
        "登录端口异常用户",
        "3个月零流量用户",
    ),
)

# 忽略第一行表头，并取出一列数据（Series）
s_dycj = pd.read_excel(
    f"data/1-当月拆机/{year}{month}_当月拆机.xlsx",
    skiprows=1,
    header=None,
    dtype="string",
)[0]
s_dyxz = pd.read_excel(
    f"data/1-当月新装/{year}{month}_当月新装.xlsx",
    skiprows=1,
    header=None,
    dtype="string",
)[0]
s_xjzj = pd.read_excel(
    f"data/1-虚假装机/{year}{month}_虚假装机.xlsx",
    skiprows=1,
    header=None,
    dtype="string",
)[0]

# 结算系数数据
df_jsxs = pd.read_excel(
    f"data/1-结算系数/{year}{month}_企宽难度与家宽乡村地址列表.xlsx",
    usecols=[5, 6, 10],
    dtype="string",
)
df_jsxs.columns = ["5级地址ID", "专业类型", "结算系数"]
df_jsxs["结算系数"] = df_jsxs["结算系数"].astype("float")


# %%
# 资源不准确存量用户（市）
df["人工稽核结果"] = S_NOT_PASSED
df["资源不准确存量用户（市）"] = S_NOT_PASSED
df_hg_ok_ziyuan = df_hg[df_hg["错误类型"] == "正确资源"][S_ACCOUNT]
df.loc[
    df[S_ACCOUNT].isin(df_hg_ok_ziyuan), "资源不准确存量用户（市）"
] = S_PASSED

# %%
# 一址多户用户（5户及以上）（市）
df["一址多户用户（5户及以上）（市）"] = S_PASSED
df_gx_err_addr = df_gx.groupby("七级地址ID").filter(
    lambda x: len(x) >= 5
)[S_ACCOUNT]
df.loc[
    df[S_ACCOUNT].isin(df_gx_err_addr), "一址多户用户（5户及以上）（市）"
] = S_NOT_PASSED
df_gx_ok_addr = df_gx.groupby("七级地址ID").filter(
    lambda x: len(x) == 1
)[S_ACCOUNT]
df.loc[
    df[S_ACCOUNT].isin(df_gx_ok_addr), "一址多户用户（5户及以上）（市）"
] = S_PASSED

# %%
# 登录端口异常用户（市）
df["登录端口异常用户（市）"] = S_PASSED
df_hg_err_port = df_hg.groupby(
    ["ONU下联端口", "所属ONU设备名称"]
).filter(lambda x: len(x) >= 2)[S_ACCOUNT]
df.loc[
    df[S_ACCOUNT].isin(df_hg_err_port), "登录端口异常用户（市）"
] = S_NOT_PASSED
df_hg_ok_port = df_hg.groupby(
    ["ONU下联端口", "所属ONU设备名称"]
).filter(lambda x: len(x) == 1)[S_ACCOUNT]
df.loc[
    df[S_ACCOUNT].isin(df_hg_ok_port), "登录端口异常用户（市）"
] = S_PASSED

# %%
# 3个月零流量用户（市）
df["3个月零流量用户（市）"] = S_PASSED
mask = (
    (df_bi["T减2月流量"] <= 5)
    & (df_bi["T减1月流量"] <= 5)
    & (df_bi["T月流量"] <= 5)
)
df_bi_err_liuliang = df_bi.loc[mask, S_ACCOUNT]
df.loc[
    df[S_ACCOUNT].isin(df_bi_err_liuliang), "3个月零流量用户（市）"
] = S_NOT_PASSED

df["存在当月BI"] = S_PASSED
df.loc[
    ~df[S_ACCOUNT].isin(df_bi[S_ACCOUNT]), "存在当月BI"
] = S_NOT_PASSED

df["存在当月管线"] = S_PASSED
df.loc[
    ~df[S_ACCOUNT].isin(df_gx[S_ACCOUNT]), "存在当月管线"
] = S_NOT_PASSED

# %%
# 合作模式有误或不一致, 接入模式有误或不一致
df_tmp = df[[S_ACCOUNT, "合作模式", "接入模式"]].merge(
    df_gx[[S_ACCOUNT, "合作模式", "接入模式"]],
    on=S_ACCOUNT,
    how="left",
)
df["合作模式有误或不一致"] = S_NOT_PASSED
df["接入模式有误或不一致"] = S_NOT_PASSED

mask = df[S_ACCOUNT].isin(
    df_tmp[
        (df_tmp["合作模式_x"] == "自建宽带")
        & (df_tmp["合作模式_x"] == df_tmp["合作模式_y"])
    ][S_ACCOUNT]
)
df.loc[mask, "合作模式有误或不一致"] = S_PASSED

mask = df[S_ACCOUNT].isin(
    df_tmp[
        (df_tmp["接入模式_x"].isin(["FTTB", "FTTH"]))
        & (df_tmp["接入模式_x"] == df_tmp["接入模式_y"])
    ][S_ACCOUNT]
)
df.loc[mask, "接入模式有误或不一致"] = S_PASSED


# %%
# 当月拆机, 当月新装, 虚假装机
df["当月拆机"] = S_PASSED
df.loc[df[S_ACCOUNT].isin(s_dycj), "当月拆机"] = S_NOT_PASSED

df["当月新装"] = S_PASSED
df.loc[df[S_ACCOUNT].isin(s_dyxz), "当月新装"] = S_NOT_PASSED

df["虚假装机"] = S_PASSED
df.loc[df[S_ACCOUNT].isin(s_xjzj), "虚假装机"] = S_NOT_PASSED

# %%
# 人工稽核结果, 是否结算
mask = df[CHECK_COLUMNS].isin([S_PASSED]).all(axis=1)
df.loc[mask, "人工稽核结果"] = S_PASSED

df["是否结算"] = "否"
mask = (df["系统稽核结果"] == S_PASSED) & (
    df["人工稽核结果"] == S_PASSED
)
df.loc[mask, "是否结算"] = "是"

# %%
# 结算系数
df_jsxs = df_jsxs[df_jsxs["专业类型"] == "家宽"]
df_jsxs = df_jsxs[["5级地址ID", "结算系数"]]
df = df.merge(df_jsxs, on="5级地址ID", how="left")
df = df.fillna(value={"结算系数": 1.0})
# %%
# 地市稽核.zip
df.to_csv(
    f"report/{year}{month}_地市稽核.zip",
    compression=dict(
        method="zip",
        archive_name=f"{year}{month}_地市稽核.csv",
    ),
    index=False,
    encoding="utf-8-sig",
)
# %%
# 地市稽核_转移说明.xlsx
mask = (df["系统稽核结果"] == S_PASSED) & (
    df["人工稽核结果"] == S_NOT_PASSED
)
df_report = df.loc[mask, ("账期", "宽带号码")]
df_report["系统自动稽核结果"] = S_PASSED
df_report["转移状态"] = S_NOT_PASSED


def explain(x):
    reasons = []
    for col in CHECK_COLUMNS:
        if x[col] == S_NOT_PASSED:
            reasons.append(col)

    return ";".join(reasons)


df_report["转移描述"] = df.apply(explain, axis=1)
df_report.to_excel(
    f"report/{year}{month}_地市稽核-转移说明.xlsx",
    index=False,
)

# %%
# 地市稽核-分项稽核统计.xlsx
report = df[CHECK_COLUMNS].T
report[S_NOT_PASSED] = (report == S_NOT_PASSED).sum(axis=1)
report[S_PASSED] = (report == S_PASSED).sum(axis=1)

report = report[[S_PASSED, S_NOT_PASSED]]

report["总数"] = report.apply(lambda x: sum(x), axis=1)
report.to_excel(
    f"report/{year}{month}_地市稽核-分项稽核统计.xlsx",
)

# %%
# 地市稽核-分类结算量统计.xlsx
df.fillna("").pivot_table(
    index=["业务类型", "接入模式"],
    columns=["是否结算"],
    aggfunc="size",
).to_excel(f"report/{year}{month}_地市稽核-分类结算量统计.xlsx")

# %%
# 完成
